![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Traditional Disks The layout for the OLTP system is quite straightforward. A minimal configuration looks something like this:
Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, Advanced Disk I/O Concepts, remember that you can only push a disk drive to a maximum random I/O rate. With traditional disk drives, you can stripe data among the various disks using either Oracle striping or OS striping. (I consider OS striping to be the same as using a disk array, as described in the following section.) To stripe a table across multiple disks, you must first create the tablespace with multiple data files: CREATE TABLESPACE striped_tablespace DATAFILE 'file1' SIZE 1M, 'file2' SIZE 1M, 'file3' SIZE 1M, 'file4' SIZE 1M; In this example, file1 is on disk 1, file2 is on disk 2, and so on. Once the tablespace is created, you cause the table to be striped when you create the table by making the number of extents equal to the number of data files: CREATE TABLE striped_table ( column1 number(4), column2 varchar2(40) ) TABLESPACE striped_tablespace STORAGE ( INITIAL 512K NEXT 512K, MINEXTENTS 4 PCTINCREASE 0 ); In this example, the table is striped across all four of the data files. The problem with striping in this manner is that the size of the stripes is the size of the extent. Until all four extents have data in them, you access only one of the extents. It can be difficult to manage the striping of hundreds of data files across hundreds of disks and to balance the load across these disks. If some tables are extremely hot (frequently accessed), you should stripe that table across many disk drives; cold tables (those infrequently accessed) can be striped across fewer disk drives. It is the twin difficulty of configuration and load balancing that makes me prefer a disk array. When you use a disk array, these tasks are greatly simplified. Disk Arrays The layout for the OLTP system on a disk array is much simpler than it is on traditional disk drives. A minimal configuration looks something like this:
Both the data files and the indexes should be striped over as many disk drives as necessary to achieve optimal I/O rates on those disks. From Chapter 14, Advanced Disk I/O Concepts, remember that you can only push a disk drive to a maximum random I/O rate. Unlike traditional disk drives, a disk array automatically stripes the data among all the disk drives. Therefore, you have to create only one tablespace and table for all your data. Although it is not necessary to put indexes into another tablespace, I recommend doing so for other reasons (such as monitoring and maintenance). With traditional disk partitioning, it is difficult to manage hundreds of data files and hundreds of disks; with a disk array, on the other hand, it is possible to manage hundreds of disks with just a few data files. Of course, Oracle has a 2 gigabyte limitation on the size of a data file, but this is easily resolved by creating a data file for every 2 gigabytes of space you need. The data files can all reside on the same disk array volume. The fact that some tables are hot and others are cold is irrelevant because all data access is uniformly distributed among all the disks. Load balancing is not an issue because the small size of the stripe ensures that random data accesses are most likely spread out among all the disks in the array. By using a disk array, you greatly simplify many of the management and load balancing tasks. With the disk array, you also have the option of using fault tolerance without affecting system performance. Of course, using fault tolerance requires significantly more disks. I recommend that you use a disk array if possible. Software striping is fine, but if your system is under heavy loadsas is typical in an OLTP systemyou achieve better performance by offloading the striping overhead to a hardware RAID controller.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |